/*NOTE: This is model code to illustrate the logic used to generate the datasets for analysis in the SCARP pilot.
This code is not intended to run as is - due to Epic restrictions on the sharing of intellectual property all 
of the table and attribute names have been "masked" with names enclosed in square brackets if they were pulled 
from Clarity (EPIC).*/


/*****************************************************
PROJECT:  BRIDGE C2 
PILOT: SCRAP
DATE: 9/15/2022


PROJECT DESCRIPTION: Use ASCEND approach to compare CRC, CVC and 
BC cancer prevention services in patients with
	No SDH Screening
	Documented Need
	Documented No Need
Stratified by SDH need: Food, Housing, Transportation
STUDY PERIOD: 7/1/2016-2/29/2020		

DETAILS (this file): code CVC outcomes:
Outcomes:
	UTD at index: done 
	Months UTD over observation period
			Determine UTD status for end of month for each month in study
			Summarize months_utd, months in study
	Receipt of screening order while due (Poisson with months after due as offset?)
			start of first period due, first screen after due
			check distribution of time until screened 
		

INPUT DATA:
scrap.BC_patient_covariates
scrap.BC_status_at_index
scrap.BC_SCREEN_Dates
Scrap.BC_order_dates

OUTPUT DATA:
SCRAP.BC_UTD_by_month
SCRAP.BC_analysis_file


SECTIONS:
A.  Update scrap.BC_patient_covariates
		Add status_at_index, 
		>BC_analysis
B.UTD status
	Months UTD over observation period
			Determine UTD status for end of month for each month in study>SCRAP.CVC_UTD_by_month
			Summarize months_utd, months_in_study, study_offset
			Add to BC_analysis

C.Receipt of screening order while due (logistic regression)
	start of first period due, 
	length of obs after due
			-check distribution of time until obs end 
(limit to patients with minimum year after date due at index)
	first order after due (include screen dates for screens captured only in HMT or Surg hx?)

D.Among patients with order, 
	Among patients with order, receipt of screen while due(Poisson with months after due as offset)

****************************************************/;
options compress=yes reuse=yes;
libname SCRAP "e:\\sasroot\BRIDGE C2\SCRAP\";
libname RD odbc noprompt="dsn=research_dev;Trusted_Connection=yes" bulkload=yes;
/****************************************************/

/****************************************************
A.  Update scrap.CVC_patient_covariates
/****************************************************/
/*		Add status_at_index, */
proc sql;
create table analysis as
select c.*,
	s.status_at_index,
	s.date_due_at_index,
	s.ever_due 
from 
	scrap.BC_patient_covariates c left join 
	scrap.BC_status_at_index s
	on c.raw_patid=s.raw_patid;


proc sort nodup data=scrap.BC_patient_covariates
out=covariates dupout=dups;
by raw_patid;
run;

proc sql;
create table analysis as
select c.*,
	s.status_at_index,
	s.date_due_at_index,
	s.ever_due 
from 
	covariates c left join 
	scrap.BC_status_at_index s
	on c.raw_patid=s.raw_patid;


/****************************************************
B.UTD status
/****************************************************/
/*	Months UTD over observation period*/
	/*Determine UTD status for end of month for each month in study>SCRAP.CVC_UTD_by_month*/
*create patient base: one rcord for each report date between index and observation_end;

data patients;
set scrap.BC_patient_covariates; 
keep raw_patid index_visit observation_end;
run;

data patient_months;
set patients;
format report_date  date9.;
do i=1 to 44;
	report_date=intnx('month','01JUL2016'd,i-1,'end');
	if report_date>=index_visit and report_date<=observation_end then output;
end;
run;

proc freq data=patient_months;
tables observation_end;
run;

proc sql;
create table pat_screens as 
	select p.*,
	s.screen_date,
	s.next_due
from
patient_months p left join 
	scrap.BC_screen_dates s on
		p.raw_patid=s.raw_patid
and s.screen_date<=p.report_date;
quit;

proc sql;
create table utd_status as
select raw_patid,
report_date,
max(next_due) as next_due format date9.,
max(next_due)>report_date as UTD
from pat_screens group by raw_patid, report_date
order by raw_patid, report_date;

proc freq data=utd_status;
tables report_date;
run;


	data SCRAP.BC_UTD_by_month;
	set utd_status;
	run;


	/*Summarize months_utd, months_in_study, study_offset*/
	proc sql;
	create table months as 
	select raw_patid,
	count(distinct report_date) as months_in_study,
	log(count(distinct report_date)) as study_offset,
	sum(UTD) as months_UTD
	from SCRAP.CVC_UTD_by_month group by raw_patid;

	/*Add to analysis*/
	proc sql;
	create table analysis2 as
	select a.*, 
	m.months_UTD,
	m.months_in_study,
	m.study_offset
	from analysis a left join months m
	on a.raw_patid=m.raw_patid;


proc sgplot data=analysis2;
vbox months_UTD/group=sdh_food;
run;
proc sgplot data=analysis2;
vbox months_UTD/group=sdh_housing;
run;
proc sgplot data=analysis2;
vbox months_UTD/group=sdh_transportation;
run;

/****************************************************/
/*C.Receipt of screening order while due (logistic regression)*/
/****************************************************/
/*	length of obs after due*/
/*-check distribution of time from first_due to observation to obs end */
data ever_due;
set analysis2;
where ever_due=1;
format one_year_due date9.;
time_after_due=round(yrdif(date_due_at_index,observation_end, 'age'),0.01);
One_year_due=intnx('year',date_due_at_index,1,'same');
run;
proc sgplot data=ever_due;
histogram time_after_due;
run;


/*check for screens before date_due_at_index*/
proc sql;
create table predue_screens as
select e.raw_patid,
e.date_due_at_index,
e.one_year_due,
e.observation_end,
max(s.next_due) as new_due_date format date9.
from ever_due e left join 
scrap.PAP_screen_dates s
on e.raw_patid=s.raw_patid and
s.screen_date>e.index_visit and
s.screen_date<e.date_due_at_index
group by e.raw_patid,
e.date_due_at_index,
e.one_year_due,
e.observation_end;
proc sql;
create table ever_due2 as
select e.*,
max(p.new_due_date,e.date_due_at_index) as first_due_date format date9.
from ever_due e left join predue_screens p
on e.raw_patid=p.raw_patid;
*update observation after due;
data ever_due2;
set ever_due2;
time_after_due=round(yrdif(first_due_date,observation_end, 'age'),0.01);
One_year_due=intnx('year',first_due_date,1,'same');
if time_after_due<1 then delete;
run;

data HMTSHX_screens;
set scrap.BC_SCREEN_Dates;
where Lab_re_records=0 and ord_proc_records=0;
run;

proc sql;
create table orders as 
select e.raw_patid,
e.date_due_at_index,
o.order_date,
s.screen_date as hmtshx_screen_date format date9.
from ever_due2 e left join 
scrap.BC_mammogram_order_dates o 
	on e.raw_patid=o.raw_patid and
	o.order_date between e.first_due_date and
	e.one_year_due
left join 
HMTSHX_screens s 
	on e.raw_patid=s.raw_patid and
	s.screen_date between e.first_due_date and
	e.one_year_due;
data orders;
set orders;
sort_date=order_date;
if sort_date=. then sort_date=hmtshx_screen_date;
run;
proc sort data=orders;
by raw_patid sort_date ;
run;
data first_order;
set orders;
by raw_patid;
if first.raw_patid then output;
run;

data first_order;
set first_order;
format order_date2 date9.;
order_date2=order_date;
if sort_date^=. then do;
	if order_date2=. then order_date2=HMTSHX_screen_date;
end;
run;
proc datasets library=work;
save analysis2 ever_due2 first_order;
quit;
proc print data=first_order;
where order_date^=order_date2;
var order_date HMTSHX_screen_date order_date2;
run;

proc sql;
create table analysis3 as
select a.*,
e.one_year_due,
e.first_due_date,
f.order_date2 as order_date
from analysis2 a left join ever_due2 e
on a.raw_patid=e.raw_patid
left join first_order f
on a.raw_patid=f.raw_patid;
quit;

proc datasets library=work;
save analysis3;
quit;


/****************************************************/
/*D.Among patients with order, */
/****************************************************/

/*	Among patients with order, receipt of screen while due(Poisson with months after due as offset)*/

proc sql;
create table screens_while_due as
select a.raw_patid,
a.first_due_date,
a.observation_end,
s.screen_date
from analysis3 a left join 
scrap.BC_SCREEN_Dates	s
on a.raw_patid=s.raw_patid
and s.screen_date between a.first_due_date and a.observation_end
where a.order_date^=.
order by raw_patid, screen_date;


data first_screen;
set screens_while_due;
by raw_patid;
where screen_date^=.;
if first.raw_patid then output;
run;


proc sql;
create table analysis4 as
select a.*,
f.screen_date as first_screen_date format date9.
from analysis3 a left join first_screen f
on a.raw_patid=f.raw_patid;

/*time to first_screen*/
data analysis4;
set analysis4;
if first_screen_date^=. then do;
	time_to_screen=first_screen_date-first_due_date;
end;
run;


data SCRAP.BC_analysis;
set analysis4;
if first_due_date^=. then do;
	order_1y=order_date^=.;
end;
if order_date^=. then do;
	if first_screen_date=. then do;
		screen_1yr=0;
		screen_6m=0;
	end;
	if first_screen_date^=. then do;
		screen_1yr=time_to_screen<=366;
		screen_6m=time_to_screen<=(366/2);;
	end;
end;
run;
proc freq data=scrap.bc_analysis;
tables order_1y*screen_1yr/missing;
run;



proc sql;
create table visits as
select p.raw_patid,
p.index_visit,
count(distinct e.encounterid) as visits_first_yr
from SCRAP.BC_analysis p left join
scrap.scrap_encounters e
on p.raw_patid=e.raw_patid and
p.delivery_site_id=p.delivery_site_id
and e.contact_date between p.index_visit and p.index_visit+365
group by p.raw_patid,
p.index_visit;

proc sql;
create table PCvisits as
select p.raw_patid,
p.index_visit,
count(distinct e.encounterid) as PC_visits_first_yr
from SCRAP.BC_analysis p left join
scrap.scrap_potential_cohort_PC_enc e
on p.raw_patid=e.raw_patid and
p.delivery_site_id=p.delivery_site_id
and e.contact_date between p.index_visit and p.index_visit+365
group by p.raw_patid,
p.index_visit;




proc sql;
create table analysis as select a.*,
v.visits_first_yr,
pc.PC_visits_first_yr
from SCRAP.BC_analysis a left join visits v
on a.raw_patid=v.raw_patid
left join PCvisits pc
on a.raw_patid=pc.raw_patid;
quit;

/*finalize form of variables for analysis*/

data SCRAP.BC_analysis;
set analysis;
Due=status_at_index='Due';
female=sex_r='F';
race_ethnic2=race_ethnic;
if race_ethnic in('Nonhispanic Other',
	'Unknown',' ') then race_ethnic2='Other/Unknown';
FPL_cat2=FPL_cat;
if FPL_cat='<=100' then FPL_cat2='<=200';
insurance_cat=payor_type_research;
if insurance_cat in('Other Public', 'Medicaid') then insurance_cat='Medicaid/Other Public';
months_offset=log(months_in_study);
run;
